<?php
	// thong tin post
$yesterday=date("Y-m-d", time() - 86400);
	//$email1=$_REQUEST['mail1'];
	//$email2=$_REQUEST['mail2'];
	//$email1='buitrinhminhtuan@yahoo.com';
	//$email2='orchipro@gmail.com';
	
	//SELECT l.*,COUNT(l.`status`),u.email FROM log_viewer l,user_info u WHERE l.ins_time >'2012-12-12 00:00:00' AND l.ins_time<'2012-12-12 23:59:59' and l.id=u.id GROUP BY l.`status`,l.id;


	
	$to1=$email1;
	$to2=$email2;
// file 03-gmail.php

//NhÃºng thÆ° viá»‡n phpmailer
/*require_once('class.phpmailer.php');

//Khá»Ÿi táº¡o Ä‘á»‘i tÆ°á»£ng
$mail = new PHPMailer();

$mail->IsSMTP(); // Gá»�i Ä‘áº¿n class xá»­ lÃ½ SMTP
//$mail->Host       = "mail.zend.vn"; // tÃªn SMTP server
$mail->SMTPDebug  = 2;                    // enables SMTP debug information (for testing)
                                           // 1 = errors and messages
                                           // 2 = messages only
$mail->SMTPAuth   = true;                  // Sá»­ dá»¥ng Ä‘Äƒng nháº­p vÃ o account
$mail->SMTPSecure = "ssl";
$mail->Host       = "smtp.gmail.com";     // Thiáº¿t láº­p thÃ´ng tin cá»§a SMPT
$mail->Port       = 465;                     // Thiáº¿t láº­p cá»•ng gá»­i email cá»§a mÃ¡y
$mail->Username   = "elisoftmailserver@gmail.com"; // SMTP account username
$mail->Password   = "1234567892727";                   // SMTP account password

//Thiet lap thong tin nguoi gui va email nguoi gui
$mail->SetFrom('elisoftmailserver@gmail.com',"EliRemote-$yesterday");


$mail->AddAddress($to1, "klkim");
$mail->AddAddress($to2, "meokisa");
$mail->Subject    =  "List email is used in $yesterday";
$mail->CharSet = "utf-8";
$mail->IsHTML(true);*/



$connection = mysql_connect("1.234.20.151", "cam", "5678" ) ;
mysql_select_db("eliremote", $connection);
mysql_query("set names 'utf8'");
//SELECT l.server_name,l.server_ip FROM log_server l WHERE l.id='66' AND l.ins_time > '2012-12-26 00:00:00' AND l.ins_time < '2012-12-26 23:59:59' GROUP BY l.server_name;
$query="SELECT log.*,COUNT(log.`status`) as sl
		FROM (SELECT l.*,u.email 
			  FROM log_viewer l,user_info u 
			  WHERE l.ins_time >'$yesterday 00:00:00' 
			  		AND l.ins_time<'$yesterday 23:59:59' 
			  		AND l.id=u.id 
			  ORDER BY l.ins_time DESC) as log 
		GROUP BY log.`status`,log.id; ";


$result=mysql_query($query);
$mang=array();
$mangserver=array();
while($row=mysql_fetch_array($result)){
	
	
	
	if($row['status']=='login'){
		$mang[$row['id']]=array($row['email'],$row['ins_time'],$row['sl'],0,'');
		
		$q="SELECT l.server_name,l.server_ip,l.ins_time FROM log_server l WHERE l.id='".$row['id']."' AND l.ins_time > '$yesterday 00:00:00' AND l.ins_time < '$yesterday 23:59:59'  GROUP BY l.server_name order by l.server_name";
		
		$re=mysql_query($q);
		$mangserver[]=array($row['id'],$row['email'],'test','test');
		while($r=mysql_fetch_array($re)){
			$mangserver[]=array($row['id'],$row['email'],$r['server_name'],$r['server_ip'],$r['ins_time']);
		}
	}
	if($row['status']=='logout'){
		$mang[$row['id']][0]=$row['email'];
		$mang[$row['id']][4]=$row['ins_time'];
		$mang[$row['id']][3]=$row['sl'];
	}
}

$body ="

<style type='text/css'>
* {font-family:Arial, Helvetica, sans-serif; font-size:13px}
.stab {border-collapse:collapse; width:600px; border:1px solid #c4c0c9}
.stab tr:hover {background:#eeefff}
.stab tr td {padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;}
.stab tr.trtit {background:#eee}
.stab tr.trtit td {font-weight:bold;}
</style>
List viewer login/logout:<br/>
<table cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:600px; border:1px solid #c4c0c9' >
	<tr style='background:#eee'>
		<td style='font-weight:bold;text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>No</td>
		<td style='font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Email</td>
		<td style='font-weight:bold;text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Last login time</td>
		<td style='font-weight:bold;text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Login times</td>
		<td style='font-weight:bold;text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Logout times</td>
	</tr>";
		 $no=0;
		 foreach ($mang as $value =>$id) {
			$no+=1;
			$body.="<tr>
				<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$no."</td>
				<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$id[0]."</td>
				<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$id[1]."</td>
				<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$id[2]."</td>
				<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$id[3]."</td>
			</tr>";
		}
$body.="</table>";

$body.="<br/><br/>";
foreach ($mang as $value1 =>$id) {
	$temp="";
	$temp="<table cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:800px; border:1px solid #c4c0c9;' >
		<tr style='background:#eee'>
			<td style='width: 25px;font-weight:bold;text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>No</td>
			<td style='width: 25px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>ID</td>
			<td style='width: 150px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Email</td>
			<td style='font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Name Server</td>
			<td style='width: 100px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>IP Server</td>
			<td style='width: 130px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Time</td>
		</tr>";
	$no=0;
	foreach ($mangserver as $value ) {
		if($value1==$value[0]){
		
			if($value[2]!='test' and $value[3] !='test'){
				$no=$no+1;
				$temp.= "<tr>
							<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$no."</td>
							<td style='text-align: center;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$value[0]."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$value[1]."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$value[2]."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$value[3]."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$value[4]."</td>
						</tr>";
			}
		}
	}
	$temp.= "</table>";
	$temp.= "<br/><br/>";
	
	$body.=$id[0]." have ".$no." servers connect <br/>";
	$body.=$temp;
 }
 
 $query="SELECT * 
 		 FROM log_viewer_to_server lvs 
 		 WHERE lvs.ins_time >'$yesterday 00:00:00' 	AND lvs.ins_time<'$yesterday 23:59:59' 
 		 ORDER BY lvs.id,lvs.ins_time ;";
$result=mysql_query($query);

$temp="
List viewer connent to server <br/>
<table cellpadding='0' cellspacing='0' style='border-collapse:collapse; width:712px; border:1px solid #c4c0c9;' >
		<tr style='background:#eee'>
			<td style='width: 25px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>ID</td>
			<td style='width: 100px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Viewer_ip</td>
			<td style='width: 100px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Server_ip</td>
			<td style='width: 100px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Status</td>
			<td style='width: 130px;font-weight:bold;padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>Time</td>
		</tr>";


while($row=mysql_fetch_array($result)){
	$temp.= "<tr>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$row['id']."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$row['viewer_ip']."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$row['server_ip']."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$row['status']."</td>
							<td style='padding:5px; border-bottom: 1px solid #C4C0C9; border-right: 1px solid #C4C0C9;'>".$row['ins_time']."</td>
						</tr>";

}

$temp.= "</table>";
$temp.= "<br/><br/>";
$body.=$temp;

mysql_close($connection);


echo $body;
/*$mail->Body = $body;


if(!$mail->Send()) {
  echo "Mailer Error: " . $mail->ErrorInfo;
} else {
  $today=date("Y-m-d", time());
  echo "Mail sent Eliremote! ".$today;
  //$today=date("Y-m-d", time());
  //echo "<br><p style='width:100%;'>Mail was sent to ".$email1." on ".$today." </p>";
}*/

?>

